package dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;
import java.util.Set;
import dao.DAOConnection;

import models.Exercicio;
import models.GrupoMuscular;
import models.Usuario;


public class DAOUsuario {

	public Set<Usuario> select() throws Exception {
		Set<Usuario> objs = new HashSet<Usuario>();
		try {

			String query = "select idUsuario, nome, cpf, endereco, numero, bairro, cidade, uf, idPerfil, cref, telefone, email, login, senha from usuario";

			Statement statement = DAOConnection.getConnection().createStatement();

			ResultSet resultSet = statement.executeQuery(query);

			while(resultSet.next()) {
				Usuario obj = new Usuario();
				obj.idUsuario = resultSet.getInt(1);
				obj.nome = resultSet.getString(2);
				obj.cpf = resultSet.getString(3);
				obj.endereco = resultSet.getString(4);
				obj.numero = resultSet.getInt(5);
				obj.bairro = resultSet.getString(6);
				obj.cidade = resultSet.getString(7);
				obj.uf = resultSet.getString(8);
				obj.idPerfil = resultSet.getInt(9);
				obj.cref = resultSet.getInt(10);
				obj.telefone = resultSet.getInt(11);
				obj.email = resultSet.getString(12);
				obj.login = resultSet.getString(13);
				obj.senha = resultSet.getString(14);
				objs.add(obj);
			}
		} catch(SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			DAOConnection.closeConnection();
		}
		return objs; 
	}

	public Usuario select(int id) throws Exception {
		Usuario obj = null;
		Set<Usuario> objs = new HashSet<Usuario>();
		try	{

			String query = "select idUsuario, nome, cpf, endereco, numero, bairro, cidade, uf, idPerfil, cref, telefone, email, login, senha from usuario where idUsuario = ?";

			PreparedStatement statement = DAOConnection.getConnection().prepareStatement(query);
			statement.setInt(1, id);
			ResultSet resultSet = statement.executeQuery();

			while(resultSet.next()) {
				obj = new Usuario();
				obj.idUsuario = resultSet.getInt(1);
				obj.nome = resultSet.getString(2);
				obj.cpf = resultSet.getString(3);
				obj.endereco = resultSet.getString(4);
				obj.numero = resultSet.getInt(5);
				obj.bairro = resultSet.getString(6);
				obj.cidade = resultSet.getString(7);
				obj.uf = resultSet.getString(8);
				obj.idPerfil = resultSet.getInt(9);
				obj.cref = resultSet.getInt(10);
				obj.telefone = resultSet.getInt(11);
				obj.email = resultSet.getString(12);
				obj.login = resultSet.getString(13);
				obj.senha = resultSet.getString(14);
				objs.add(obj);
			}
		} catch(SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			DAOConnection.closeConnection();
		}
		return obj; 
	}
	
	public Usuario autenticar(Usuario u) throws Exception {
		Usuario obj = null;
		Set<Usuario> objs = new HashSet<Usuario>();
		try	{

			String query = "select idUsuario, nome, cpf, endereco, numero, bairro, cidade, uf, idPerfil, cref, telefone, email, login, senha from usuario where login = ? AND senha = ?";

			PreparedStatement statement = DAOConnection.getConnection().prepareStatement(query);
			statement.setString(1, u.login);
			statement.setString(2, u.senha);
			ResultSet resultSet = statement.executeQuery();

			while(resultSet.next()) {
				obj = new Usuario();
				obj.idUsuario = resultSet.getInt(1);
				obj.nome = resultSet.getString(2);
				obj.cpf = resultSet.getString(3);
				obj.endereco = resultSet.getString(4);
				obj.numero = resultSet.getInt(5);
				obj.bairro = resultSet.getString(6);
				obj.cidade = resultSet.getString(7);
				obj.uf = resultSet.getString(8);
				obj.idPerfil = resultSet.getInt(9);
				obj.cref = resultSet.getInt(10);
				obj.telefone = resultSet.getInt(11);
				obj.email = resultSet.getString(12);
				obj.login = resultSet.getString(13);
				obj.senha = resultSet.getString(14);
				objs.add(obj);
			}
		} catch(SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			DAOConnection.closeConnection();
		}
		return obj; 
	}
	
	public void insert(Usuario f) throws Exception {
		try {
			String insert = "insert into usuario(nome, cpf, endereco, numero, bairro, cidade, uf, idPerfil, cref, telefone, email, login, senha) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

			PreparedStatement statement = DAOConnection.getConnection().prepareStatement(insert);

			statement.setString(1, f.nome);
			statement.setString(2, f.cpf);
			statement.setString(3, f.endereco);
			statement.setInt(4, f.numero);
			statement.setString(5, f.bairro);
			statement.setString(6, f.bairro);
			statement.setString(7, f.uf);
			statement.setInt(8, f.idPerfil);
			statement.setInt(9, f.cref);
			statement.setInt(10, f.telefone);
			statement.setString(11, f.email);
			statement.setString(12, f.login);
			statement.setString(13, f.senha);

			statement.executeUpdate();
		} catch(SQLException e) {	
			throw new Exception();
		} finally {
			DAOConnection.closeConnection();
		}

	}

	public void update(Usuario f) throws Exception {
		try {

			String update = "update usuario set nome = ?, cpf = ?, endereco = ?, numero = ?, bairro = ?, cidade = ?, uf = ?, idPerfil = ?, cref = ?, telefone = ?, email = ?, login = ?, senha = ? where idUsuario = ?";

			PreparedStatement statement = DAOConnection.getConnection().prepareStatement(update);

			statement.setString(1, f.nome);
			statement.setString(2, f.cpf);
			statement.setString(3, f.endereco);
			statement.setInt(4, f.numero);
			statement.setString(5, f.bairro);
			statement.setString(6, f.bairro);
			statement.setString(7, f.uf);
			statement.setInt(8, f.idPerfil);
			statement.setInt(9, f.cref);
			statement.setInt(10, f.telefone);
			statement.setString(11, f.email);
			statement.setString(12, f.login);
			statement.setString(13, f.senha);
			statement.setInt(14, f.idUsuario);

			statement.executeUpdate();
		} catch(SQLException e) {
			throw new Exception();
		} finally {
			DAOConnection.closeConnection();
		}

	}

	public void delete(Usuario f) throws Exception {
		try {

			String delete = "delete from usuario where idUsuario = ?";

			PreparedStatement statement = DAOConnection.getConnection().prepareStatement(delete);

			statement.setInt(1, f.idUsuario);

			statement.executeUpdate();
		} catch(SQLException e) {
			throw new Exception();
		} finally {
			DAOConnection.closeConnection();
		}
	}
	
	public Set<Usuario> selectXNomeEPerfil(Usuario usuario) throws Exception {
		
		Set<Usuario> objs = new HashSet<Usuario>();
		usuario.nome = "%"+ usuario.nome +"%";

		try {

			String query = "SELECT u.idUsuario, u.nome "
					+"FROM Usuario u "
					+"WHERE u.nome LIKE ? "
					+"	AND idPerfil > ?";
			

			PreparedStatement ps = DAOConnection.getConnection().prepareStatement(query);
			ps.setString(1, usuario.nome);
			ps.setInt(2, usuario.idPerfil);
			

			ResultSet resultSet = ps.executeQuery();

			while(resultSet.next()) {
				Usuario obj = new Usuario();
				
				obj.idUsuario = resultSet.getInt(1);
				obj.nome = resultSet.getString(2);
				objs.add(obj);
			}

		} catch(SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			DAOConnection.closeConnection();
		}
		return objs; 
	}

}
